/**
 * 项目名：五位一体化工安全信息平台
 * 日期：    2019-11-27 02:38:44
 * Copyright (c) 2015- joysuch-版权所有
 */

package com.joysuch.wwyt.bp.repository;

import com.joysuch.wwyt.bp.entity.BpDeviceFacilityCollection;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * BpDeviceFacilityCollectionDao
 */
public interface BpDeviceFacilityCollectionDao
        extends JpaRepository<BpDeviceFacilityCollection, Long>, QuerydslPredicateExecutor<BpDeviceFacilityCollection> {
    public Page<BpDeviceFacilityCollection> findAll(Specification<BpDeviceFacilityCollection> spec, Pageable pageable);

    public BpDeviceFacilityCollection save(BpDeviceFacilityCollection bean);

    public void delete(BpDeviceFacilityCollection bean);

    public List<BpDeviceFacilityCollection> findByMajorHazardId(Long id);

    @Query("select distinct(m) from BpDeviceFacilityCollection m join BpDeviceFacilityMonitoringPoint b on m.id = b.deviceId where b.monitoringPointId=?1 and m.deleteFlag='0' and b.deleteFlag='0' ")
    public List<BpDeviceFacilityCollection> queryMonitoringPointBindDevice(Long id);

    public BpDeviceFacilityCollection findFirstByDeviceTypeNameAndDeviceId(String deviceTypeName, Long deviceId);

    public List<BpDeviceFacilityCollection> findByDeviceTypeNameAndDeviceIdIn(String deviceTypeName, List<Long> deviceId);

    public BpDeviceFacilityCollection findFirstByDeviceId(Long deviceId);

    public void deleteByDeviceTypeNameAndDeviceId(String deviceTypeName, Long deviceId);

    // @Query("select name from BpDeviceFacilityCollection where id=:id")
    //todo 设备设施做未做假删除的暂时处理
    @Query(value = "SELECT NAME FROM wwyt_bp_device_facility_collection WHERE (DELETE_FLAG='0' OR DELETE_FLAG='1') AND ID =?1", nativeQuery = true)
    String findNameById(Long id);

    @Query(value = "SELECT * FROM wwyt_bp_device_facility_collection WHERE ID IN( SELECT DISTINCT DEVICE_ID FROM wwyt_bp_device_facility_monitoring_point WHERE DELETE_FLAG = '0' AND ORG_CODE LIKE :orgCode%) AND ID <= " +
            "(SELECT ID FROM wwyt_bp_device_facility_collection WHERE ID IN( SELECT DISTINCT DEVICE_ID FROM wwyt_bp_device_facility_monitoring_point WHERE DELETE_FLAG = '0' AND ORG_CODE LIKE :orgCode%) AND DELETE_FLAG = '0' AND ORG_CODE LIKE :orgCode% ORDER BY ID DESC LIMIT :number ,1) " +
            " AND DELETE_FLAG = '0'AND ORG_CODE LIKE :orgCode% ORDER BY ID DESC LIMIT :pageSize", nativeQuery = true)
    List<BpDeviceFacilityCollection> getListBySize(@Param("number") int number, @Param("pageSize") int pageSize, @Param("orgCode") String orgCode);

    @Query(value = "SELECT * FROM wwyt_bp_device_facility_collection WHERE ID IN( SELECT DISTINCT DEVICE_ID FROM wwyt_bp_device_facility_monitoring_point WHERE DELETE_FLAG = '0' AND ORG_CODE LIKE :orgCode%) AND ID <= " +
            "(SELECT ID FROM wwyt_bp_device_facility_collection WHERE ID IN( SELECT DISTINCT DEVICE_ID FROM wwyt_bp_device_facility_monitoring_point WHERE DELETE_FLAG = '0' AND ORG_CODE LIKE :orgCode%) AND DELETE_FLAG = '0' AND ORG_CODE LIKE :orgCode% ORDER BY ID DESC LIMIT :number ,1) " +
            " AND DEVICE_TYPE_ID !=4 AND DELETE_FLAG = '0'AND ORG_CODE LIKE :orgCode% ORDER BY ID DESC LIMIT :pageSize", nativeQuery = true)
    List<BpDeviceFacilityCollection> getListBySizeAndExcludeType(@Param("number") int number, @Param("pageSize") int pageSize, @Param("orgCode") String orgCode);

    @Query(value = "SELECT count(*) FROM wwyt_bp_device_facility_collection WHERE ID IN( SELECT DISTINCT DEVICE_ID FROM wwyt_bp_device_facility_monitoring_point WHERE DELETE_FLAG = '0' AND ORG_CODE LIKE %:orgCode%) AND DELETE_FLAG = '0'", nativeQuery = true)
    int countNumber(@Param("orgCode") String orgCode);

    @Query(value = "SELECT * FROM wwyt_bp_device_facility_collection WHERE DELETE_FLAG = '0' ORDER BY ID DESC LIMIT ?1", nativeQuery = true)
    List<BpDeviceFacilityCollection> getListLimitSize(Integer pageSize);

    @Query(value = "SELECT count(*) FROM wwyt_bp_device_facility_collection WHERE ID IN( SELECT DISTINCT DEVICE_ID FROM wwyt_bp_device_facility_monitoring_point WHERE DELETE_FLAG = '0' AND ORG_CODE LIKE %:orgCode%) AND DELETE_FLAG = '0' AND NAME LIKE %:name%", nativeQuery = true)
    int countNumberByName(@Param("orgCode") String orgCode, @Param("name") String name);

    @Query(value = "SELECT * FROM wwyt_bp_device_facility_collection WHERE id =(SELECT DEVICE_ID FROM wwyt_bp_device_facility_monitoring_point WHERE MONITORING_POINT_ID = ?1 AND delete_flag = '0' LIMIT 1) AND delete_flag = '0'", nativeQuery = true)
    BpDeviceFacilityCollection getDeviceTypeByPointId(Long id);

    @Query(value = "SELECT * FROM wwyt_bp_device_facility_collection WHERE ID IN (SELECT DISTINCT DEVICE_ID FROM wwyt_bp_device_facility_monitoring_point WHERE DELETE_FLAG = '0' AND ORG_CODE LIKE :orgCode%) AND DEVICE_TYPE_ID !=4 AND DELETE_FLAG = '0'", nativeQuery = true)
    List<BpDeviceFacilityCollection> getAllFacillityAndExcludeType(String orgCode);
    
    BpDeviceFacilityCollection findFirstByCode(String code);

    @Query(value = "select * from wwyt_bp_device_facility_collection where id = ?1",nativeQuery = true)
    BpDeviceFacilityCollection getCollectionIdById(Long id);

    @Query(value = "select * from wwyt_bp_device_facility_collection where code = ?1 and DEVICE_TYPE_NAME =?2 and DELETE_FLAG= '0'",nativeQuery = true)
    BpDeviceFacilityCollection findByCodeAndTypeName(String code, String deviceTypeName);

    @Modifying
    @Query(value = "UPDATE wwyt_bp_device_facility_collection SET major_hazard_id = null WHERE major_hazard_id =?1 AND delete_flag = '0'", nativeQuery = true)
    void deleteByMajorHazardId(Long majorHazardId);
}
